SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

</head>

SQL/XML

</h1>

</header>

<div id="mw-content-text" class="mw-body-content mw-content-ltr" lang="en" dir="ltr"><div class="mw-content-ltr mw-parser-output" lang="en" dir="ltr">SQL/XML or XML-Related Specifications is part 14 of the <a href="../Structured_Query_Language" class="mw-redirect" title="Structured Query Language">Structured Query Language</a> (SQL) specification. In addition to the traditional predefined SQL data types like <code>NUMERIC, CHAR, TIMESTAMP, ... </code> it introduces the predefined data type <code>XML</code> together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL <a href="../Database" title="Database">database</a>.

<ul><li><a href="../SQL%3A2003" title="SQL:2003">SQL:2003-14</a> defines the basic datatype, mappings, predicates and functions.</li>

<li><a href="../SQL%3A2006" title="SQL:2006">SQL:2006-14</a>, <a href="../SQL%3A2008" title="SQL:2008">SQL:2008-14</a> and <a href="../SQL%3A2011" title="SQL:2011">SQL:2011-14</a> expands the older versions and approaches to <a href="../XQuery" title="XQuery">XQuery</a>.<a href="#cite_note-funderburk-1">[1]</a></li></ul>

<div class="mw-heading mw-heading2"><h2 id="Specification">Specification</h2></div>

The specification defines the data type <code>XML</code>, functions for working with XML, including element construction, mapping data from relational tables, combining XML fragments, and embedding <a href="../XQuery" title="XQuery">XQuery</a> expressions in <a href="../SQL" title="SQL">SQL</a> statements. Functions which can be embedded include XMLQUERY (which extracts XML or values from an XML field) and XMLEXISTS (which predicates whether an XQuery expression is matched).

Further information and examples of the SQL/XML functions are provided in the external links below<a href="#cite_note-wagner-2">[2]</a><a href="#cite_note-melton-3">[3]</a><a href="#cite_note-eisenberg-4">[4]</a>.

<div class="mw-heading mw-heading2"><h2 id="Standard_compliance">Standard compliance</h2></div>

The result of Wagner's objective evaluation of the SQL/XML:2006 standard compliance of Oracle 11g Release 1, MS SQL Server 2008 and MySQL 5.1.30 is shown in the following table<a href="#cite_note-wagner-2">[2]</a>, to which the data for PostgreSQL 9.1,<a href="#cite_note-5">[5]</a><a href="#cite_note-6">[6]</a> and IBM DB2 has been added:

<th>Oracle 11g Release 1</th>

<th>MS SQL Server 2008</th>

<th>MySQL 5.1.30</th>

<th>PostgreSQL 9.1

</th></tr>

<tr>

<th>Datatype XML

</th>

<td style="background:#FFB; color:black;vertical-align:middle;text-align:center;" class="table-partial">Partial (Oracle entitles the data type 'XMLType' instead of 'XML')</td>

<td style="background:#FFB; color:black;vertical-align:middle;text-align:center;" class="table-partial">Partial

</td></tr>

<tr>

<th>SQL/XML predicates

</th>

<td style="background:#FFB; color:black;vertical-align:middle;text-align:center;" class="table-partial">Partial</td>

<td style="background:#FFB; color:black;vertical-align:middle;text-align:center;" class="table-partial">Partial

</td></tr>

<tr>

<th>SQL/XML functions

</th>

<td style="background:#FFB; color:black;vertical-align:middle;text-align:center;" class="table-partial">Partial</td>

<td>High

</td></tr>

<tr>

<th><a href="../XQuery" title="XQuery">XQuery</a> augmentation

</th>

<td style="background:#FFC7C7;color:black;vertical-align:middle;text-align:center;" class="table-no">No

</td></tr></tbody></table>

<div class="mw-heading mw-heading2"><h2 id="Examples">Examples</h2></div>

The sample SQLXML query below has SQLXML type as output(tested on DB2 9.7 and Oracle 11g):

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT XMLELEMENT(NAMEs "PhoneBook", -- root element name

XMLAGG( -- aggregation over the rows

XMLELEMENT(NAME "Contact",

XMLATTRIBUTES(cust.FIRST_NAME AS "Name",

cust.TEL)

)

FROM TMP.CUSTOMER AS cust;

</pre></div>

And the output:

</PhoneBook>

</pre></div>

Samples are taken from javalobby article<a href="#cite_note-kec-7">[7]</a>.

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("../mw/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("../mw/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("../mw/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("../mw/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}

/* end https://en.wikipedia.org/ */

</style><cite id="CITEREFFunderburk,_J._E.Malaika,_S.Reinwald,_B.2002" class="citation cs2">Funderburk, J. E.; Malaika, S.; Reinwald, B. (2002), <a rel="nofollow" class="external text" href="http://ict.udlap.mx/people/carlos/is346/files/reinwald.pdf">"XML programming with SQL/XML and XQuery"</a> (PDF), IBM Systems Journal, 41 (4): 642–665, <a href="../Doi_(identifier)" class="mw-redirect" title="Doi (identifier)">doi</a>:<a rel="nofollow" class="external text" href="https://doi.org/10.1147%2Fsj.414.0642">10.1147/sj.414.0642</a>, retrieved 14 November 2011</cite>

</li>

<li id="cite_note-wagner-2">^ <a href="#cite_ref-wagner_2-0">a</a> <a href="#cite_ref-wagner_2-1">b</a> <cite id="CITEREFWagner,_Michael2010" class="citation cs2">Wagner, Michael (2010), "1. Auflage", <a rel="nofollow" class="external text" href="https://books.google.com/books?id=HJH-WAPNcNgC">SQL/XML:2006 - Evaluierung der Standardkonformität ausgewählter Datenbanksysteme</a>, Diplomica Verlag, <a href="../ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>978-3-8366-9609-8</bdi></cite>

</li>

<li id="cite_note-melton-3"><a href="#cite_ref-melton_3-0">^</a> <cite id="CITEREFEisenberg,_AndrewMelton,_Jim2004" class="citation cs2">Eisenberg, Andrew; Melton, Jim (2004), <a rel="nofollow" class="external text" href="http://www.sigmod.org/publications/sigmod-record/0409/11.JimMelton.pdf">"Advancements in SQL/XML"</a> (PDF), SIGMOD Record, 33 (3): 79, <a href="../Doi_(identifier)" class="mw-redirect" title="Doi (identifier)">doi</a>:<a rel="nofollow" class="external text" href="https://doi.org/10.1145%2F1031570.1031588">10.1145/1031570.1031588</a>, retrieved 14 November 2011</cite>

</li>

<li id="cite_note-eisenberg-4"><a href="#cite_ref-eisenberg_4-0">^</a> <cite id="CITEREFEisenberg,_AndrewMelton,_Jim2002" class="citation cs2">Eisenberg, Andrew; Melton, Jim (2002), <a rel="nofollow" class="external text" href="http://www.sigmod.org/publications/sigmod-record/0206/standard.pdf">"SQL/XML is Making Good Progress"</a> (PDF), SIGMOD Record, 31 (2): 101, <a href="../CiteSeerX_(identifier)" class="mw-redirect" title="CiteSeerX (identifier)">CiteSeerX</a> <a rel="nofollow" class="external text" href="https://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.109.4867">10.1.1.109.4867</a>, <a href="../Doi_(identifier)" class="mw-redirect" title="Doi (identifier)">doi</a>:<a rel="nofollow" class="external text" href="https://doi.org/10.1145%2F565117.565141">10.1145/565117.565141</a>, retrieved 14 November 2011</cite>

</li>

<li id="cite_note-5"><a href="#cite_ref-5">^</a> <a rel="nofollow" class="external text" href="http://www.postgresql.org/docs/9.1/static/features.html">PostgreSQL Conformance with ISO 9075-14 (SQL/XML)</a>, at PostgreSQL 9.1 documentation.

</li>

<li id="cite_note-6"><a href="#cite_ref-6">^</a> <a rel="nofollow" class="external text" href="http://www.postgresql.org/docs/current/static/functions-xml.html">PostgreSQL 9.1 XML functions</a>, at PostgreSQL 9.1 documentation.

</li>

<li id="cite_note-kec-7"><a href="#cite_ref-kec_7-0">^</a> <cite id="CITEREFKec,_Daniel2013" class="citation cs2">Kec, Daniel (2013), SQLX - From DB Straight to XML and Back, Javalobby.org</cite>

</li>

</ol></div>

<div class="mw-heading mw-heading2"><h2 id="External_links">External links</h2></div>

<ul><li><a rel="nofollow" class="external text" href="http://www.postgresql.org/docs/current/static/functions-xml.html">SQL/XML on PostgreSQL</a></li>

<li><a rel="nofollow" class="external text" href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb13gen.htm#g1047191">SQL/XML on Oracle 11g</a></li></ul>

/* start https://en.wikipedia.org/ */

.mw-parser-output .hlist dl,.mw-parser-output .hlist ol,.mw-parser-output .hlist ul{margin:0;padding:0}.mw-parser-output .hlist dd,.mw-parser-output .hlist dt,.mw-parser-output .hlist li{margin:0;display:inline}.mw-parser-output .hlist.inline,.mw-parser-output .hlist.inline dl,.mw-parser-output .hlist.inline ol,.mw-parser-output .hlist.inline ul,.mw-parser-output .hlist dl dl,.mw-parser-output .hlist dl ol,.mw-parser-output .hlist dl ul,.mw-parser-output .hlist ol dl,.mw-parser-output .hlist ol ol,.mw-parser-output .hlist ol ul,.mw-parser-output .hlist ul dl,.mw-parser-output .hlist ul ol,.mw-parser-output .hlist ul ul{display:inline}.mw-parser-output .hlist .mw-empty-li{display:none}.mw-parser-output .hlist dt::after{content:": "}.mw-parser-output .hlist dd::after,.mw-parser-output .hlist li::after{content:" · ";font-weight:bold}.mw-parser-output .hlist dd:last-child::after,.mw-parser-output .hlist dt:last-child::after,.mw-parser-output .hlist li:last-child::after{content:none}.mw-parser-output .hlist dd dd:first-child::before,.mw-parser-output .hlist dd dt:first-child::before,.mw-parser-output .hlist dd li:first-child::before,.mw-parser-output .hlist dt dd:first-child::before,.mw-parser-output .hlist dt dt:first-child::before,.mw-parser-output .hlist dt li:first-child::before,.mw-parser-output .hlist li dd:first-child::before,.mw-parser-output .hlist li dt:first-child::before,.mw-parser-output .hlist li li:first-child::before{content:" (";font-weight:normal}.mw-parser-output .hlist dd dd:last-child::after,.mw-parser-output .hlist dd dt:last-child::after,.mw-parser-output .hlist dd li:last-child::after,.mw-parser-output .hlist dt dd:last-child::after,.mw-parser-output .hlist dt dt:last-child::after,.mw-parser-output .hlist dt li:last-child::after,.mw-parser-output .hlist li dd:last-child::after,.mw-parser-output .hlist li dt:last-child::after,.mw-parser-output .hlist li li:last-child::after{content:")";font-weight:normal}.mw-parser-output .hlist ol{counter-reset:listitem}.mw-parser-output .hlist ol>li{counter-increment:listitem}.mw-parser-output .hlist ol>li::before{content:" "counter(listitem)"\a0 "}.mw-parser-output .hlist dd ol>li:first-child::before,.mw-parser-output .hlist dt ol>li:first-child::before,.mw-parser-output .hlist li ol>li:first-child::before{content:" ("counter(listitem)"\a0 "}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r1236075235">

/* start https://en.wikipedia.org/ */

.mw-parser-output .navbox{box-sizing:border-box;border:1px solid #a2a9b1;width:100%;clear:both;font-size:88%;text-align:center;padding:1px;margin:1em auto 0}.mw-parser-output .navbox .navbox{margin-top:0}.mw-parser-output .navbox+.navbox,.mw-parser-output .navbox+.navbox-styles+.navbox{margin-top:-1px}.mw-parser-output .navbox-inner,.mw-parser-output .navbox-subgroup{width:100%}.mw-parser-output .navbox-group,.mw-parser-output .navbox-title,.mw-parser-output .navbox-abovebelow{padding:0.25em 1em;line-height:1.5em;text-align:center}.mw-parser-output .navbox-group{white-space:nowrap;text-align:right}.mw-parser-output .navbox,.mw-parser-output .navbox-subgroup{background-color:#fdfdfd}.mw-parser-output .navbox-list{line-height:1.5em;border-color:#fdfdfd}.mw-parser-output .navbox-list-with-group{text-align:left;border-left-width:2px;border-left-style:solid}.mw-parser-output tr+tr>.navbox-abovebelow,.mw-parser-output tr+tr>.navbox-group,.mw-parser-output tr+tr>.navbox-image,.mw-parser-output tr+tr>.navbox-list{border-top:2px solid #fdfdfd}.mw-parser-output .navbox-title{background-color:#ccf}.mw-parser-output .navbox-abovebelow,.mw-parser-output .navbox-group,.mw-parser-output .navbox-subgroup .navbox-title{background-color:#ddf}.mw-parser-output .navbox-subgroup .navbox-group,.mw-parser-output .navbox-subgroup .navbox-abovebelow{background-color:#e6e6ff}.mw-parser-output .navbox-even{background-color:#f7f7f7}.mw-parser-output .navbox-odd{background-color:transparent}.mw-parser-output .navbox .hlist td dl,.mw-parser-output .navbox .hlist td ol,.mw-parser-output .navbox .hlist td ul,.mw-parser-output .navbox td.hlist dl,.mw-parser-output .navbox td.hlist ol,.mw-parser-output .navbox td.hlist ul{padding:0.125em 0}.mw-parser-output .navbox .navbar{display:block;font-size:100%}.mw-parser-output .navbox-title .navbar{float:left;text-align:left;margin-right:0.5em}body.skin--responsive .mw-parser-output .navbox-image img{max-width:none!important}@media print{body.ns-0 .mw-parser-output .navbox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r920966791">

/* start https://en.wikipedia.org/ */

.mw-parser-output span.smallcaps{font-variant:small-caps}.mw-parser-output span.smallcaps-smaller{font-size:85%}

/* end https://en.wikipedia.org/ */

</style></div><div role="navigation" class="navbox" aria-labelledby="SQL70" style="padding:3px"><table class="nowraplinks mw-collapsible autocollapse navbox-inner" style="border-spacing:0;background:transparent;color:inherit"><tbody><tr><th scope="col" class="navbox-title" colspan="2"><style data-mw-deduplicate="TemplateStyles:r1239400231">

/* start https://en.wikipedia.org/ */

.mw-parser-output .navbar{display:inline;font-size:88%;font-weight:normal}.mw-parser-output .navbar-collapse{float:left;text-align:left}.mw-parser-output .navbar-boxtext{word-spacing:0}.mw-parser-output .navbar ul{display:inline-block;white-space:nowrap;line-height:inherit}.mw-parser-output .navbar-brackets::before{margin-right:-0.125em;content:"[ "}.mw-parser-output .navbar-brackets::after{margin-left:-0.125em;content:" ]"}.mw-parser-output .navbar li{word-spacing:-0.125em}.mw-parser-output .navbar a>span,.mw-parser-output .navbar a>abbr{text-decoration:inherit}.mw-parser-output .navbar-mini abbr{font-variant:small-caps;border-bottom:none;text-decoration:none;cursor:inherit}.mw-parser-output .navbar-ct-full{font-size:114%;margin:0 7em}.mw-parser-output .navbar-ct-mini{font-size:114%;margin:0 4em}html.skin-theme-clientpref-night .mw-parser-output .navbar li a abbr{color:var(--color-base)!important}@media(prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .navbar li a abbr{color:var(--color-base)!important}}@media print{.mw-parser-output .navbar{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><div id="SQL70" style="font-size:114%;margin:0 4em"><a href="../SQL" title="SQL">SQL</a></div></th></tr><tr><th scope="row" class="navbox-group" style="width:1%">Versions</th><td class="navbox-list-with-group navbox-list navbox-odd hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="../SEQUEL" class="mw-redirect" title="SEQUEL">SEQUEL</a></li>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%"><a href="../SQL_reserved_words" class="mw-redirect" title="SQL reserved words">Keywords</a></th><td class="navbox-list-with-group navbox-list navbox-even hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<li><a href="../Create_(SQL)" class="mw-redirect" title="Create (SQL)">Create</a></li>

<li><a href="../Delete_(SQL)" title="Delete (SQL)">Delete</a></li>

<li><a href="../Group_by_(SQL)" title="Group by (SQL)">Group by</a></li>

<li><a href="../Having_(SQL)" title="Having (SQL)">Having</a></li>

<li><a href="../Insert_(SQL)" title="Insert (SQL)">Insert</a></li>

<li><a href="../Merge_(SQL)" title="Merge (SQL)">Merge</a></li>

<li><a href="../Order_by" title="Order by">Order by</a></li>

<li><a href="../Prepare_(SQL)" class="mw-redirect" title="Prepare (SQL)">Prepare</a></li>

<li><a href="../Select_(SQL)" title="Select (SQL)">Select</a></li>

<li><a href="../Truncate_(SQL)" title="Truncate (SQL)">Truncate</a></li>

<li><a href="../Set_operations_(SQL)" title="Set operations (SQL)">Union</a></li>

<li><a href="../Update_(SQL)" title="Update (SQL)">Update</a></li>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Related</th><td class="navbox-list-with-group navbox-list navbox-odd hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="../Edgar_F._Codd" title="Edgar F. Codd">Edgar Codd</a></li>

<li><a href="../Relational_database" title="Relational database">Relational database</a></li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">ISO/IEC SQL parts</th><td class="navbox-list-with-group navbox-list navbox-even hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li>Framework</li>

<li>Foundation</li>

<li><a href="../SQL/CLI" class="mw-redirect" title="SQL/CLI">Call-Level Interface</a></li>

<li><a href="../SQL/PSM" title="SQL/PSM">Persistent Stored Modules</a></li>

<li><a href="../SQL/MED" title="SQL/MED">Management of External Data</a></li>

<li><a href="../SQL/OLB" title="SQL/OLB">Object Language Bindings</a></li>

<li><a href="../SQL/Schemata" title="SQL/Schemata">Information and Definition Schemas</a></li>

<li><a href="../SQL/JRT" title="SQL/JRT">SQL Routines and Types for the Java Programming Language</a></li>

</ul>

</div></td></tr></tbody></table></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .asbox{position:relative;overflow:hidden}.mw-parser-output .asbox table{background:transparent}.mw-parser-output .asbox p{margin:0}.mw-parser-output .asbox p+p{margin-top:0.25em}.mw-parser-output .asbox-body{font-style:italic}.mw-parser-output .asbox-note{font-size:smaller}.mw-parser-output .asbox .navbar{position:absolute;top:-0.75em;right:1em;display:none}.mw-parser-output :not(p):not(.asbox)+style+.asbox,.mw-parser-output :not(p):not(.asbox)+link+.asbox{margin-top:3em}

/* end https://en.wikipedia.org/ */

</style></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2023-03-28" href="https://en.wikipedia.org/wiki/?title=SQL%2FXML&oldid=1147037091">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>